using System;
using System.Collections.Generic;
//using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    //public class TienTrongNgayDao : IDaoObject<TienTrongNgayDto, int>
    public class TienTrongNgayDao
    {

        #region IDaoObject<TienTrongNgayDto,int> Members

        public TienTrongNgayDto SaveOrUpdate(TienTrongNgayDto obj)
        {
            OleDbConnection connection = MyConnection.ConnectionData();
            OleDbTransaction transaction = connection.BeginTransaction();
            try
            {
                transaction.Begin();
                //insert into Tientrongngay table
                string sqlPattern = " insert into Tientrongngay(Ngayban,Tongtien)" + 
                    "values({0},{1})";
                string sqlString = String.Format(sqlPattern , obj.NgayBan , obj.TongTien );
                return obj;
            }
            catch (Exception)
            {
                transaction.Rollback();
            }
            return null;
        }

        public TienTrongNgayDto SaveOrUpdate(TienTrongNgayDto obj, OleDbTransaction transaction)
        {
            throw new NotImplementedException();
        }

        public void Delete(int id)
        {
            OleDbConnection connection = MyConnection.ConnectionData();
            string strSQL = "Delete from Tientrongngay where ID = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, connection);
            cmd.Parameters.Add("@Id", OleDbType.Integer);
            cmd.Parameters["@Id"].Value = id;
            cmd.ExecuteNonQuery();
            connection.Close();
        }

        public TienTrongNgayDto Select(int id)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "select * from Tientrongngay where ID = '"+id+"' ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            TienTrongNgayDto ttn = new TienTrongNgayDto();
            while (dr.Read())
            {
                ttn.Id = Convert.ToInt32(dr["ID"].ToString ());
                ttn.NgayBan = Convert.ToDateTime(dr["Ngayban"].ToString());
                ttn.TongTien = Convert.ToSingle(dr["Tongtien"].ToString ());
            }
            cn.Close();
            dr.Close();
            return ttn;
        }

        public TienTrongNgayDto[] SelectAll()
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strsql = "select * from Tientrongngay";
            OleDbCommand cmd = new OleDbCommand(strsql,cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            List<TienTrongNgayDto> ttndto = new List<TienTrongNgayDto>() ;
            while (dr.Read())
            {
                TienTrongNgayDto ttn = new TienTrongNgayDto();
                ttn.Id = Convert .ToInt32 ( dr["ID"].ToString ());
                ttn.NgayBan = Convert.ToDateTime(dr["Ngayban"].ToString());
                ttn.TongTien = Convert.ToSingle(dr["Tongtien"].ToString ());
                ttndto .Add (ttn);               
            }
            cn.Close();
            dr.Close();
            return ttndto.ToArray();
            
        }

        #endregion

        public static TienTrongNgayDto TimKiemTheoNgay(TienTrongNgayDto obj)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "Select * From Tientrongngay Where Ngayban=?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ngayban", OleDbType.Date);
            cmd.Parameters["@Ngayban"].Value = obj.NgayBan;
            cmd.ExecuteNonQuery();
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                obj.Id = Convert.ToInt32(dr["ID"].ToString());
                obj.NgayBan = Convert.ToDateTime(dr["Ngayban"].ToString());
                obj.TongTien = Convert.ToSingle(dr["Tongtien"].ToString());
            }
            cn.Close();
            dr.Close();
            return obj;
        }

        public static void CapNhatTienTrongNgay(TienTrongNgayDto obj)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "Update Tientrongngay Set Tongtien=? Where Id=?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Tongtien", OleDbType.Integer);
            cmd.Parameters["@Tongtien"].Value = obj.TongTien;
            cmd.Parameters.Add("@Id", OleDbType.Integer);
            cmd.Parameters["@Id"].Value = obj.Id;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public static TienTrongNgayDto ThemTienTrongNgay(TienTrongNgayDto obj)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "Insert into Tientrongngay(Ngayban,Tongtien) values(?,?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ngayban", OleDbType.Date);
            cmd.Parameters["@Ngayban"].Value = obj.NgayBan;
            cmd.Parameters.Add("@Tongtien", OleDbType.Integer);
            cmd.Parameters["@Tongtien"].Value = obj.TongTien;
            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            obj.Id = (int)cmd.ExecuteScalar();
            cn.Close();
            return obj;
            //
        }
    }
}
